package com.wondersgroup.fjzlpt.org.dao.impl.fwb;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.page.PageQuerySQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.quick.framework.boot.repository.sql.jdbcTemplate.rs.ResultHandlerConstant;
import com.wondersgroup.fjzlpt.org.dao.inter.fwb.FwbDAO;
@Repository
public class FwbDAOImpl extends BaseJTDAOImpl implements FwbDAO{
	
	@Autowired
	private DialectProxy dialectProxy;
	
	@Override
	@Autowired
	@Qualifier("TB_XXWH_FWBJBXX")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}

	@Value("${common.WSGLJGLBDM}")
	private String GLJGLBDM;

	/**
	 * 分页查询
	 */
	public JSONObject queryPage(Map<String, String> map) {
		JSONObject sqlObj = this.getPageSql(map);
		String sql = sqlObj.getString("sql");
		List<String> list = (List)sqlObj.get("sqlParams");
		
		PageQuerySQLBean sb = super.checkAndCreatePageQuerySQLBean(map, sql, "order by a.CJSJ DESC", list.toArray());
		return super.queryPage(sb, ResultHandlerConstant.JSONARRAY);
	}
	
	/**
	 * 根据条件查询所有数据
	 * @param map
	 * @return
	 */
	public JSONArray queryList(Map<String, String> map){
		JSONObject sqlObj = this.getPageSql(map);
		String sql = sqlObj.getString("sql") + " order by a.CJSJ DESC";
		List<String> list = (List)sqlObj.get("sqlParams");
		
		SQLBean bean = new SQLBean(sql, list.toArray());
		return super.queryList(bean);
	}
	
	/**
	 * 合成基本分页sql
	 * @param map
	 * @return
	 */
	private JSONObject getPageSql(Map<String, String> map){
		JSONObject result = new JSONObject();
		
		StringBuilder sbud = new StringBuilder("select a.*,b.YLJGMC from TB_XXWH_FWBJBXX a, TB_ZD_YLJGXX b \n");
		sbud.append(" where a.YLJGID = b.PTJGID  \n");
		List list = new ArrayList();
		String value = map.get("FWBMC");
		if(!StringUtils.isEmpty(value))
		{
			sbud.append("and a.FWBMC like ? ");
			list.add("%"+value+"%");
		}
		value = map.get("FWBZT");
		if(!StringUtils.isEmpty(value))
		{
			sbud.append("and a.FWBZT = ? ");
			list.add(value);
		}
		//医疗机构名称
		value = map.get("YLJGMC");
		if(!StringUtils.isEmpty(value))
		{
			sbud.append("and b.YLJGMC like ? ");
			list.add("%"+value+"%");
		}

		/** 医疗机构代码和行政区划代码的条件，只能2选一，且已医疗机构代码优先 */
		if(!StringUtils.isEmpty(value = map.get("YLJGID"))){
			sbud.append(" and ( a.YLJGID = ? or  a.XJJGID = ? ) ");
			list.add(value);
			list.add(value);
		}else if(!StringUtils.isEmpty(value = map.get("XZQHDM")))
		{
			/*int length = value.length();
			if(length == 6){
				sbud.append("and a.XZQHDM in(?,?,?)");
				list.add(value);
				list.add(value.substring(0, 4));
				list.add(value.substring(0, 2));
			}
			else if(length == 4){
				sbud.append("and a.XZQHDM in(?,?) ");
				list.add(value);
				list.add(value.substring(0, 2));
			}
			else if(length == 2){
				sbud.append("and a.XZQHDM = ?");
				list.add(value);
			}*/
			sbud.append(" and ( ");
			int count = value.length()/2;
			List xzqhSqlList = new ArrayList(count);
			for(int i = 0 ; i < count ;i ++ ){
				String xzqh = value.substring(0 , (i+1)*2);
				xzqhSqlList.add(" (a.XZQHDM = ? and a.JGLB like ?) ");
				if(i==3){ //"街道"行政区划
					xzqh = value.substring(0,(i+1)*2+1);
				}
				list.add(xzqh);
				list.add(GLJGLBDM+"%");
			}
			sbud.append(String.join(" or ", xzqhSqlList));

			//添加本机构id为条件
			sbud.append(" or a.YLJGID = ? or  a.XJJGID = ? ");
			list.add(value = map.get("YLJGID_this"));
			list.add(value);
			//是否管理机构（是：查询其下级）
			String JGLB = map.get("JGLB");
			if(JGLB != null && JGLB.indexOf(GLJGLBDM) != -1){
				sbud.append(" or a.XZQHDM like ?");
				list.add(map.get("XZQHDM")+"%");
			}
			sbud.append(" ) ");
		}
		
		result.put("sql", sbud.toString());
		result.put("sqlParams", list);
		
		return result;
	}

	/**
	 * 根据多个id查询服务包
	 * @param map
	 * @return
	 */
	/*public JSONArray queryList(String[] fwbidAry) {
		StringBuilder sbud = new StringBuilder("select a.FWBID,a.FWBMC,a.YLJGID,a.YXQ,a.FY,a.FWBZT,b.YLJGMC,DATE_FORMAT(a.CJSJ,'%Y-%m-%d %T') CJSJ,  \n");
		sbud.append("(select XMMC from TB_ZY_ZT where XMDM = a.FWBZT) FWBZTMC,");
		sbud.append("(select XMMC from TB_ZY_YXQ where XMDM = a.YXQ) YXQMC \n");
		sbud.append(" from TB_XXWH_FWBJBXX a, TB_ZD_YLJGXX b where a.YLJGID = b.PTJGID and a.FWBID in (");
		String params = "";
		for (String string : fwbidAry) {
			params += "?,";
		}
		params = params.substring(0, params.lastIndexOf(","));
		sbud.append(params);
		sbud.append(")\n");
		SQLBean sb = new SQLBean(sbud.toString(),fwbidAry);
		return super.queryList(sb);
	}*/

	@Override
	public JSONArray queryAll(Map<String, String> map) {
		JSONArray jsonArray = null;
		if(map!=null && map.size()>0){
			StringBuilder sql = new StringBuilder("select b.FWBID,b.FWBMC,b.FWBLX,b.YBJJZF,b.SFBHYBFWX from TB_XXWH_FWBJBXX b where 1=1 \n");
			List list = new ArrayList();
			if(!StringUtils.isEmpty(map.get("FWBZT")))
			{
				sql.append("and b.FWBZT = ? ");
				list.add(map.get("FWBZT"));
			}
			if(!StringUtils.isEmpty(map.get("YLJGID")))
			{
				sql.append(" and (b.YLJGID = ? ");
				list.add(map.get("YLJGID"));
				sql.append(" or ( ");
				String JGXZQHDM = map.get("JGXZQHDM");
				int count = JGXZQHDM.length()/2;
				List xzqhSqlList = new ArrayList(count);
				for(int i = 0 ; i < count ;i ++ ){
					String xzqh = JGXZQHDM.substring(0 , (i+1)*2);
					xzqhSqlList.add(" (b.xzqhdm=? and JGLB like ?) ");
					if(i==3){ //"街道"行政区划
						xzqh = JGXZQHDM.substring(0,(i+1)*2+1);
					}
					list.add(xzqh);
					list.add("%"+GLJGLBDM+"%");
				}
				sql.append(String.join(" or ", xzqhSqlList));
				sql.append(" )) ");
			}else if(!StringUtils.isEmpty(map.get("JGXZQHDM"))){
				String JGXZQHDM = map.get("JGXZQHDM");
				int count = JGXZQHDM.length()/2;
				List xzqhSqlList = new ArrayList(count);
				for(int i = 0 ; i < count ;i ++ ){
					String xzqh = JGXZQHDM.substring(0 , (i+1)*2);
					xzqhSqlList.add(" (b.xzqhdm=? and JGLB like ?) ");
					list.add(xzqh);
					list.add("%"+GLJGLBDM+"%");
				}
				sql.append(" and(");
				sql.append(String.join(" or ", xzqhSqlList));
				sql.append(" ) ");
			}
			sql.append("order by CJSJ ");
			SQLBean sqlBean = new SQLBean(sql.toString(), list.toArray());
			jsonArray= super.queryList(sqlBean);
		}
		return jsonArray;
	}

	/**
	 * 判断是否存在相同的服务包名称
	 * @return
	 */
	@Override
	public JSONObject checkSaveName(Map<String, String> map) {
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select FWBID from TB_XXWH_FWBJBXX where FWBMC=? ");
		List<String> sqlParams = new ArrayList<String>();
		sqlParams.add(map.get("FWBMC"));
		
		String FWBID = map.get("FWBID");
		if(!StringUtils.isEmpty(FWBID)){
			sqlBuf.append("and FWBID <> ?");
			sqlParams.add(FWBID);
		}
		
		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		
		return super.queryOne(bean);
	}

	
	/**
	 * 查询服务包基本信息和服务包服务列表信息到健康城市系统
	 * @param map
	 * @return
	 */
	public JSONArray queryFwbAndFwxxListToJkcs(Map<String, String> map){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		
		JSONArray jsonArray = null;
		if(map!=null && map.size()>0){

			//StringBuilder sql = new StringBuilder("select b.FWBID,b.FWBMC,b.FY, ");
			//
			//if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			//	sql.append("(select GROUP_CONCAT(d.FWMC separator '|') from TB_XXWH_FWB_FWXX c,TB_XXWH_FWXX d where c.FWID=d.FWID and c.FWBID=b.FWBID) FWMCS,");
			//	sql.append("(select GROUP_CONCAT(d.FWNR separator '|') from TB_XXWH_FWB_FWXX c,TB_XXWH_FWXX d where c.FWID=d.FWID and c.FWBID=b.FWBID) FWNRS ");
			//}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			//	sql.append("(select listagg(d.FWMC,'|') within group(order by null) from TB_XXWH_FWB_FWXX c,TB_XXWH_FWXX d where c.FWID=d.FWID and c.FWBID=b.FWBID) FWMCS,");
			//	sql.append("(select listagg(d.FWNR,'|') within group(order by null) from TB_XXWH_FWB_FWXX c,TB_XXWH_FWXX d where c.FWID=d.FWID and c.FWBID=b.FWBID) FWNRS ");
			//}else{
			//	throw new RuntimeException("无法确定分组函数");
			//}
			//sql.append("from TB_XXWH_FWBJBXX b where 1=1 \n");

			/**
			 * 这里修改为用视图查询。
			 */
			StringBuilder sql = new StringBuilder("SELECT FWBID , FWBMC , FY , FWMCS , FWNRS FROM V_FWB_JBXX WHERE 1 = 1 ");

			List<String> list = new ArrayList<String>();
			if(!StringUtils.isEmpty(map.get("FWBZT")))
			{
				sql.append(" and FWBZT = ? ");
				list.add(map.get("FWBZT"));
			}

			sql.append(" and (YLJGID = ? ");
			list.add(map.get("YLJGID"));

			sql.append(" or ");
			String JGXZQHDM = map.get("JGXZQHDM");
			int count = JGXZQHDM.length()/2;
			List<String> xzqhSqlList = new ArrayList<String>(count);
			for(int i = 0 ; i < count ;i ++ ){
				String xzqh = JGXZQHDM.substring(0 , (i+1)*2);
				xzqhSqlList.add(" (xzqhdm = ? and JGLB like ? ) ");
				if(i==3){ //"街道"行政区划
					xzqh = JGXZQHDM.substring(0,(i+1)*2+1);
				}
				list.add(xzqh);
				list.add("%"+GLJGLBDM+"%");
			}
			sql.append(String.join(" or ", xzqhSqlList));
			sql.append(" ) ");
			
			sql.append("order by CJSJ");
			SQLBean sqlBean = new SQLBean(sql.toString(), list.toArray());
			jsonArray= super.queryList(sqlBean);
		}
		return jsonArray;
	}

	/**
	 * 查询协议可选的所有服务包--V_XXWH_FWBXX
	 * @return
	 */
	public JSONArray queryAllByView(Map<String, String> map){
		StringBuffer sqlBuf = new StringBuffer("select * from V_XXWH_FWBXX a where 1=1 ");
		List sqlParams = new ArrayList();

		String value = map.get("FWBIDS");
		if(!StringUtils.isEmpty(value)){
			sqlBuf.append("and a.FWBID in(");
			String[] fwbidArr = value.split("\\|");
			for(int i=0;i<fwbidArr.length;i++){
				sqlBuf.append("?,");
				sqlParams.add(fwbidArr[i]);
			}
			sqlBuf.deleteCharAt(sqlBuf.length()-1).append(") ");
		}

		SQLBean bean = new SQLBean(sqlBuf.toString(), sqlParams.toArray());
		JSONArray fwbArr = super.queryList(bean);
		return fwbArr;
	}

}
